package com.Model.Database;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.content.Context;

public class Database extends SQLiteOpenHelper {
	
	private static final String TAG = "Database";
	
	// when you want to upgrade the database, increase this number by 1
	// and the onUpgrade method will run

	private static final int DATABASE_VERSION = 32;  //was 31     

	private static final String DATABASE_NAME = "StudentNotebook";
	
	public Database(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating tables for our application
	// If you make changes to the schema then change the DATABASE_VERSION number
	// and the onUpgrade method will be called
	private void createAllTables(SQLiteDatabase db) {
		// Creating user table to store user information
		db.execSQL("CREATE TABLE User (FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Major varchar(50), University varchar(50))");
		// Creating courses table to store course information and faculty
		// information
		
		// Creating table preferences for storing the user preferences
		db.execSQL("CREATE TABLE Preferences (startapp varchar(5), trackgrades varchar(5))");
		
		db.execSQL("CREATE TABLE Courses (CourseId varchar(10) NOT NULL PRIMARY KEY, CourseName varchar(25) NOT NULL, Section varchar(4), Location varchar(25), Credit varchar(2), ProfEmail varchar(20), Website text(255), Day varchar(20), StartTime varchar(10), EndTime varchar(10))");
		// Creating Class Event table to store different events such as
		// assignments, projects,midterms etc
		db.execSQL("CREATE TABLE ClassEvent (EventCourseComboId varchar(30) NOT NULL PRIMARY KEY, EventName varchar(20) NOT NULL, CourseId varchar(10) NOT NULL , EventType varchar(20), MarkReceived NUMERIC(5,2), OutOf NUMERIC(5,2), EventWeight NUMERIC(5,2), DueDate DATE NOT NULL, Percentage NUMERIC(5,2),FOREIGN KEY (CourseId) REFERENCES Courses(CourseId))");
		// Creating lab table for storing lab related information
		db.execSQL("CREATE TABLE Lab (LabName varchar(10) NOT NULL, CourseId varchar(10) NOT NULL, ProfName varchar(20), FOREIGN KEY (CourseId) REFERENCES Courses(CourseId))");
		// Creating Notes table to store notes for different courses
		db.execSQL("CREATE TABLE Notes (_id integer primary key autoincrement, title text not null, body text not null , courseId varchar(10), FOREIGN KEY (courseId) REFERENCES Courses(CourseId) );");
		// Creating the faculty table which stores faculty information for
		// different courses.
		db.execSQL("CREATE TABLE Faculty (FacCourseComboID varchar(60) NOT NULL, FacName varchar(50) NOT NULL, FacType varchar(30), FacLocation varchar(30), FacHours varchar(100), CourseId varchar(10) NOT NULL, FOREIGN KEY (CourseId) REFERENCES Courses(CourseId), PRIMARY KEY(FacCourseComboID))");
		//Create a table to store the StudentWebmail address
		db.execSQL("CREATE TABLE StudentWebmail (WebmailAddress varchar(100) NOT NULL);");
		//Create a table to store Extra Curricular Activities
		db.execSQL("CREATE TABLE Curricular (_id integer primary key autoincrement, name varchar(20) NOT NULL, day varchar(10), time varchar(10) , location varchar(20), comments varchar(20))");
		//Create a table for study Contact Info 
		db.execSQL("CREATE TABLE StudyInfo (CourseId varchar(10) NOT NULL, GroupName varchar(35) NOT NULL,GroupTime varchar(250),GroupNotes varchar(250), PRIMARY KEY (CourseId,GroupName),FOREIGN KEY (CourseId) REFERENCES Courses(CourseId))");		
		//Create a table to store ToDo 
		db.execSQL("CREATE TABLE CourseToDo(CourseId varchar(10),title text, Desc text, date varchar(10), time varchar(10),PRIMARY KEY(Desc))");
		
		//Triggers on our database

		//This trigger deletes class events for the course that is deleted.
		db.execSQL("CREATE TRIGGER delete_classEvent AFTER DELETE ON courses FOR EACH ROW BEGIN" + 
				   " DELETE FROM classEvent WHERE courseid=old.courseid;" +
				   " END; END;");
		db.execSQL("CREATE TRIGGER delete_classNote AFTER DELETE ON courses FOR EACH ROW BEGIN" + 
				   " DELETE FROM Notes WHERE courseid=old.courseid;" +
				   " END; END;");
		db.execSQL("CREATE TRIGGER delete_StudyInfo AFTER DELETE ON courses FOR EACH ROW BEGIN" + 
				   " DELETE FROM StudyInfo WHERE courseid=old.courseid;" +
				   " END; END;");
		db.execSQL("CREATE TRIGGER delete_todo AFTER DELETE ON courses FOR EACH ROW BEGIN" + 
				   " DELETE FROM CourseToDo WHERE courseid=old.courseid;" +
				   " END; END;");
	}
 	
	//drop all tables in the database
	private void dropAllTables(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS User");
		db.execSQL("DROP TABLE IF EXISTS Preferences");
		db.execSQL("DROP TABLE IF EXISTS Courses");
		db.execSQL("DROP TABLE IF EXISTS ClassEvent");
		db.execSQL("DROP TABLE IF EXISTS Lab");
		db.execSQL("DROP TABLE IF EXISTS Notes");
		db.execSQL("DROP TABLE IF EXISTS Faculty");
		db.execSQL("DROP TABLE IF EXISTS StudentWebmail");
		db.execSQL("DROP TABLE IF EXISTS CourseGrade");
		db.execSQL("DROP TABLE IF EXISTS Marks");
		db.execSQL("DROP TABLE IF EXISTS Curricular");
		db.execSQL("DROP TABLE IF EXISTS StudyInfo");
		db.execSQL("DROP TABLE IF EXISTS CourseToDo");

	}

	
	//This creates the database. When the application is loaded and the database is called this is created. 
	@Override
	public void onCreate(SQLiteDatabase db) {
		createAllTables(db);
	}
	
	// This method is called when the DATABSE_VERSION number is changed
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
        Log.w(TAG, "Upgrading database from version " + oldVer + " to "
                + newVer + ", which will destroy all old data");
        dropAllTables(db);
        onCreate(db);       
    }
		
	
}
